package com.czjtxy.extraTerm.daoImpl;

import com.czjtxy.extraTerm.dao.TnoticeDao;
import com.czjtxy.extraTerm.entity.DO.Tnotice;
import com.czjtxy.extraTerm.entity.VO.TnoticeVO;
import com.czjtxy.extraTerm.util.Util;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ZhangYing
 * @class 软件1903
 * @studentID 19852035
 * @date 2021/7/4
 */
public class TnoticeDaoImpl extends BaseDaoImpl implements TnoticeDao {
    @Override
    public List<TnoticeVO> getAll() throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "SELECT * FROM notice_inf ni LEFT JOIN user_inf ui on ui.ID = ni.USER_ID";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        List<TnoticeVO> tnoticeVOS = new ArrayList<>();
        while (rs.next()) {
            tnoticeVOS.add(conventRsToVO(rs));
        }
        return tnoticeVOS;
    }

    @Override
    public TnoticeVO getOne(int nid) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "SELECT * FROM notice_inf ni LEFT JOIN user_inf ui on ui.ID = ni.USER_ID WHERE ni.ID = ?";
        ps = con.prepareStatement(sql);
        ps.setInt(1, nid);
        rs = ps.executeQuery();
        TnoticeVO tnoticeVO = new TnoticeVO();
        if (rs.next()) {
            tnoticeVO = conventRsToVO(rs);
        }
        return tnoticeVO;
    }

    @Override
    public void updateNotice(Tnotice tnotice) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "UPDATE notice_inf SET TITLE = ?, CONTENT = ? WHERE notice_inf.ID = ?";
        ps = conventTnoticeToPs(sql, tnotice);
        ps.setInt(3, tnotice.getId());
        ps.executeUpdate();
    }

    @Override
    public void deleteDept(int nid) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "DELETE FROM notice_inf WHERE ID = ?";
        ps = con.prepareStatement(sql);
        ps.setInt(1, nid);
        ps.executeUpdate();
    }

    @Override
    public void addNotice(Tnotice tnotice) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "INSERT INTO notice_inf (TITLE, CONTENT, CREATE_DATE)" +
                "VALUES (?, ?, NOW());";
        ps = conventTnoticeToPs(sql, tnotice);
        ps.executeUpdate();
    }

    @Override
    public List<TnoticeVO> getPart(Tnotice tnotice) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "SELECT * FROM notice_inf ni LEFT JOIN user_inf ui ON ui.ID = ni.USER_ID WHERE TITLE LIKE ? AND CONTENT LIKE ?";
        ps = con.prepareStatement(sql);
        ps.setString(1, Util.likeStr(tnotice.getTitle()));
        ps.setString(2, Util.likeStr(tnotice.getContent()));
        List<TnoticeVO> tnoticeVOS = new ArrayList<>();
        rs = ps.executeQuery();
        while (rs.next()) {
            tnoticeVOS.add(conventRsToVO(rs));
        }
        return tnoticeVOS;
    }

    private PreparedStatement conventTnoticeToPs(String sql, Tnotice tnotice) throws SQLException {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, tnotice.getTitle());
        ps.setString(2, tnotice.getContent());
        return ps;
    }

    private TnoticeVO conventRsToVO(ResultSet rs) throws SQLException {
        TnoticeVO tnoticeVO = new TnoticeVO();
        tnoticeVO.setId(rs.getInt("ni.ID"));
        tnoticeVO.setTitle(rs.getString("TITLE"));
        tnoticeVO.setContent(rs.getString("CONTENT"));
        tnoticeVO.setCreateDate(rs.getDate("CREATE_DATE"));
        tnoticeVO.setUserName(rs.getString("username"));
        return tnoticeVO;
    }
}
